package com.my.myTradeData;

import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;
import java.util.List;

import org.hibernate.Query;
import org.hibernate.SQLQuery;
import org.hibernate.Session;
import org.hibernate.SessionFactory;
import org.hibernate.cfg.Configuration;

import com.my.stock.core.KLine;
import com.my.stock.core.Turtle;

public class HibernateUtil {

	private static final SessionFactory sessionFactory = buildSessionFactory();

	private static SessionFactory buildSessionFactory() {
		try {
			// Create the SessionFactory from hibernate.cfg.xml
			return new Configuration().configure().buildSessionFactory();
		} catch (Throwable ex) {
			// Make sure you log the exception, as it might be swallowed
			System.err.println("Initial SessionFactory creation failed." + ex);
			throw new ExceptionInInitializerError(ex);
		}
	}

	public static SessionFactory getSessionFactory() {
		return sessionFactory;
	}

	public static void shutdown() {
		// Close caches and connection pools
		getSessionFactory().close();
	}

	public static DayData selectTodayData(String code,Date date) {
		Session session = HibernateUtil.getSessionFactory().openSession();
		DateFormat df = new SimpleDateFormat("yyyy-MM-dd");
		String begin = df.format(date);
		Query query = session.createQuery("from DayData where dataDate='" + begin + "' and sinaCode='" + code + "'");
		List<DayData> l = query.list();
		if (l.size() > 0) {
			return l.get(0);
		} else {
			return null;
		}
	}

	public static List<TradeProduct> selectAllTradeProduct() {
		Session session = HibernateUtil.getSessionFactory().openSession();
		Query query = session.createQuery("from TradeProduct");
		List<TradeProduct> l = query.list();
		return l;
	}
	
	public static List<Turtle> selectAllTurtle() {
		Session session = HibernateUtil.getSessionFactory().openSession();
		Query query = session.createQuery("from Turtle");
		List<Turtle> l = query.list();
		return l;
	}

	public static TickData selectTopData(String code) {
		Session session = HibernateUtil.getSessionFactory().openSession();
		Query query = session.createQuery("from TickData  where code='" + code + "' order by buySellAbs desc");
		query.setMaxResults(20);
		List<TickData> l = query.list();
		return l.get(19);
	}

	public static TickData selectTopTurnOverData(String code) {
		Session session = HibernateUtil.getSessionFactory().openSession();
		Query query = session.createQuery("from TickData  where code='" + code + "' order by deltaTurnOver desc");
		query.setMaxResults(5000);
		List<TickData> l = query.list();
		return l.get(l.size() - 1);

	}

	public static List<TickData> selectAllData(String code) {
		Session session = HibernateUtil.getSessionFactory().openSession();
		Query query = session.createQuery("from TickData where code='" + code + "' and averagePrice>0 order by id");
		List<TickData> l = query.list();
		return l;
	}
	
	public static List<Double> selectAllData() {
		Session session = HibernateUtil.getSessionFactory().openSession();
		SQLQuery query = session.createSQLQuery("select averagePrice from TickData where  averagePrice>0 order by id");

		query.setMaxResults(10);
		List<Double> l = query.list();
		return l;
	}

	public static List<TickData> selectSignalData(String code, double buySellAbs) {
		Session session = HibernateUtil.getSessionFactory().openSession();
		// Query query =
		// session.createQuery("from TickData where tSignal >=0 and code='" +
		// code + "' and deltaTurnOver>"+turnOver);
		Query query = session.createQuery("from TickData where tSignal >=0 and code='" + code + "' and buySellAbs>=" + 80);
		List<TickData> l = query.list();
		return l;
	}

	public static List<TickData> searchSignal(String code, int tSignal, long beginTick, long endTick) {
		Session session = HibernateUtil.getSessionFactory().openSession();
		// Query query =
		// session.createQuery("from TickData where tSignal ="+tSignal+" and code='"
		// + code + "' and ticks>"+beginTick+" and ticks<"+endTick);
		Query query = session.createQuery("from TickData where tSignal =" + tSignal + " and code='" + code + "'");
		List<TickData> l = query.list();
		return l;
	}

	public static TickData searchSignal(double price, String code, int id) {
		Session session = HibernateUtil.getSessionFactory().openSession();
		// Query query =
		// session.createQuery("from TickData where tSignal ="+tSignal+" and code='"
		// + code + "' and ticks>"+beginTick+" and ticks<"+endTick);
		Query query = session.createQuery("from TickData where averagePrice > " + price + " and id>" + id + " and code='" + code + "'");
		query.setMaxResults(1);
		List<TickData> l = query.list();
		if (l.size() > 0) {
			return l.get(0);
		} else {
			return null;
		}

	}

	public static void updateSignal() {
		Session session = HibernateUtil.getSessionFactory().openSession();
		session.beginTransaction();
		Query query = session.createQuery("update TickData  set tSignal=-1");
		query.executeUpdate();
		query = session.createQuery("update TickData  set tSignal=0 where sellVol>200");
		query.executeUpdate();
		query = session.createQuery("update TickData  set tSignal=1 where buyVol>200");
		query.executeUpdate();
		session.getTransaction().commit();
		session.close();
	}

	public static List<TickData> selectSignalData1(String code, double turnOver) {
		Session session = HibernateUtil.getSessionFactory().openSession();
		Query query = session.createQuery("from TickData where tSignal >=0 and code='" + code + "' and deltaTurnOver>" + turnOver);
		List<TickData> l = query.list();
		return l;
	}

	public static List<TickData> selectSignalData2(String code, String id, double buyPrice) {
		Session session = HibernateUtil.getSessionFactory().openSession();
		Query query = session.createQuery("from TickData where id >" + id + " and id<=" + id + "+10 and sellPrice<=" + buyPrice + " and sellPrice>0 and code='" + code + "'");
		query.setMaxResults(1);
		List<TickData> l = query.list();
		return l;
	}

	public static List<TickData> selectSignalMax(String code, String id) {
		Session session = HibernateUtil.getSessionFactory().openSession();
		Query query = session.createQuery("from TickData where id<=" + id + " and code='" + code + "' and status=1 order by averagePrice desc");
		query.setMaxResults(50);
		List<TickData> l = query.list();
		return l;
	}

	public static List<TickData> selectSignalMin(String code, String id) {
		Session session = HibernateUtil.getSessionFactory().openSession();
		Query query = session.createQuery("from TickData where id <=" + id + " and code='" + code + "' and status=1 and averagePrice<>0 order by averagePrice");
		query.setMaxResults(50);
		List<TickData> l = query.list();
		return l;
	}

	public static List<TickData> selectSignalLow(String code, String id) {
		Session session = HibernateUtil.getSessionFactory().openSession();
		Query query = session.createQuery("from TickData where id <=" + id + " and id>" + id + "-20 and code='" + code + "' and averagePrice<>0 order by averagePrice");
		query.setMaxResults(1);
		List<TickData> l = query.list();
		return l;
	}

	public static List<TickData> selectSignalHigh(String code, String id) {
		Session session = HibernateUtil.getSessionFactory().openSession();
		Query query = session.createQuery("from TickData where id <=" + id + " and id>" + id + "-20 and code='" + code + "' order by averagePrice desc");
		query.setMaxResults(1);
		List<TickData> l = query.list();
		return l;
	}

	public static List<TickData> selectSignalData3(String code, String id, double sellPrice) {
		Session session = HibernateUtil.getSessionFactory().openSession();
		Query query = session.createQuery("from TickData where id >" + id + " and id<=" + id + "+10 and buyPrice>=" + sellPrice + " and code='" + code + "'");
		query.setMaxResults(1);
		List<TickData> l = query.list();
		return l;
	}

	public static List<TickData> selectTopAbs(String code) {
		Session session = HibernateUtil.getSessionFactory().openSession();
		SQLQuery query = session.createSQLQuery("select id,buyPrice,buyVol,sellPrice,sellVol,todayVol,todayPosition,tickDate,ticks,todayTurnOver,code,deltaTurnOver,deltaVol,tSignal,averagePrice,max(buySellabs) as buySellAbs from tickdata  GROUP BY `code` ");
		query.addEntity(TickData.class);
		List<TickData> l = query.list();
		return l;
	}

	public static List<Object[]> selectLowAbs(String code) {
		Session session = HibernateUtil.getSessionFactory().openSession();
		SQLQuery query = session.createSQLQuery("select code,min(buySellAbs) from tickdata where buySellAbs>0  GROUP BY `code` ");
		List<Object[]> l = query.list();
		return l;
	}

	public static double selectSignal(String code, String id, double time) {
		Session session = HibernateUtil.getSessionFactory().openSession();
		SQLQuery query = session.createSQLQuery("select sum(buyNumber)-sum(sellNumber) from tickdata where code='" + code + "' and id>=" + id + "-" + time + " and id<=" + id);
		List<Object> l = query.list();
		if (l != null && l.size() > 0) {
			return (Double) l.get(0);
		} else {
			return -1;
		}

	}

	public static List<TickData> selectAvgTurnOver(String code) {
		Session session = HibernateUtil.getSessionFactory().openSession();
		SQLQuery query = session.createSQLQuery("select id,buyPrice,buyVol,sellPrice,sellVol,todayVol,todayPosition,tickDate,ticks,todayTurnOver,code,deltaTurnOver,deltaVol,tSignal,averagePrice,avg(deltaturnOver) as buySellAbs from tickdata where deltaTurnOver>0   GROUP BY `code`");
		query.addEntity(TickData.class);
		List<TickData> l = query.list();
		return l;
	}

	public static List<TickData> selectRandomData(String code, double deltaTurnOver) {
		Session session = HibernateUtil.getSessionFactory().openSession();
		String ids = "";
		// for (int i = 0; i < 4111; i++) {
		// ids = ids + (randomNum(94738) + 15468135) + ",";
		// }
		Query query = session.createQuery("from TickData where code='" + code + "' and status=1");
		List<TickData> l = query.list();
		return l;
	}

	public static List<TickData> selectAllData(String code, double volMax) {
		Session session = HibernateUtil.getSessionFactory().openSession();
		Query query = session.createQuery("from TickData where code='" + code + "' and (buyVol>" + volMax + " or sellVol>" + volMax + ") order by id");
		List<TickData> l = query.list();
		return l;
	}

	public static List<TickData> selectSpeedUpSignal(String code) {
		Session session = HibernateUtil.getSessionFactory().openSession();
		Query query = session.createQuery("from TickData where code='" + code + "' and speed20-8>speed60");
		List<TickData> l = query.list();
		return l;
	}

	public static List<TickData> selectTickDataByBigThanId(int id, String code) {
		Session session = HibernateUtil.getSessionFactory().openSession();
		Query query = session.createQuery("from TickData where id>" + id + " and code='" + code + "'  order by id");
		query.setMaxResults(30);
		List<TickData> l = query.list();
		return l;

	}

	public static int randomNum(int max) {
		return (int) (Math.random() * max);
	}

	public static TickData getTickData(long date, String code) {
		Session session = HibernateUtil.getSessionFactory().openSession();
		Query query = session.createQuery("from TickData where ticks>=" + date + " and code='" + code + "'");
		query.setMaxResults(1);
		List<TickData> l = query.list();

		if (l != null && l.size() > 0) {
			return l.get(0);
		} else {
			return null;
		}

	}

	public static TickData getTickDataById1(String id1, String id, String code, double s) {
		Session session = HibernateUtil.getSessionFactory().openSession();
		Query query = session.createQuery("from TickData where id>" + id1 + " and id<=" + id + " and buyPrice>=" + s + " and code='" + code + "' and sellPrice!=1.79769e308");
		List<TickData> l = query.list();

		if (l != null && l.size() > 0) {
			return l.get(0);
		} else {
			return null;
		}

	}

	public static TickData getTickDataById(String id, String code) {
		Session session = HibernateUtil.getSessionFactory().openSession();
		Query query = session.createQuery("from TickData where id=" + id + " and code='" + code + "' and sellPrice!=1.79769e308");
		List<TickData> l = query.list();

		if (l != null && l.size() > 0) {
			return l.get(0);
		} else {
			return null;
		}

	}

	public static TickData getTickDataById2(String id1, String id, String code, double s) {
		Session session = HibernateUtil.getSessionFactory().openSession();
		Query query = session.createQuery("from TickData where id>" + id1 + " and id<=" + id + " and sellPrice<=" + s + "    and code='" + code + "' and sellPrice!=1.79769e308");
		List<TickData> l = query.list();

		if (l != null && l.size() > 0) {
			return l.get(0);
		} else {
			return null;
		}

	}

	public static List<KLine> selectDatas(String code, int paraDays,Date date) {
		Session session = HibernateUtil.getSessionFactory().openSession();
		DateFormat df = new SimpleDateFormat("yyyy-MM-dd");
		Calendar c = Calendar.getInstance();
		c.setTime(date);
		c.add(Calendar.DAY_OF_MONTH, paraDays * -1);

		String begin = df.format(c.getTime());
		String today = df.format(date);
		Query query = session.createQuery("from DayData where  name='" + code + "' and dataDate<>'" + today + "' order by dataDate desc");
		query.setMaxResults(paraDays);
		List<DayData> l = query.list();

		List<KLine> kList = new ArrayList<KLine>();
		for (DayData d : l) {
			KLine kLine = new KLine();
			kLine.setClose(d.getClose());
			kLine.setOpen(d.getOpen());
			kLine.setHigh(d.getHigh());
			kLine.setLow(d.getLow());
			kLine.setVol(d.getVol());
			kLine.setSinaCode(d.getSinaCode());
			kList.add(kLine);
		}
		return kList;
	}

	public static List<Position> selectPosition(String code) {
		Session session = HibernateUtil.getSessionFactory().openSession();
		Query query = session.createQuery("from Position where  sinaCode='" + code + "'");
		List<Position> l = query.list();
		return l;
	}
}
